<?php

namespace App\Http\Controllers\Admin;

use Dompdf\Dompdf;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Mockery\Exception;

class TestdataKeyController extends Controller
{
    private function dealstr($str){
        $str = str_replace(' ','',$str);
        return $str;
    }
    //映射关系
    public function relation(Request $request)
    {
        $id = $request->id; //a_std_tool_item_rel ID
        if ($request->isMethod('POST')) {
            $idMap = $request->id_map;
            $key_id = $request->testdata_key_id;
            //点对点映射
            if($key_id){
                DB::beginTransaction();
                try{
                    DB::table('a_testdata_key')->where('id', $key_id)->update(['id_map'=>$idMap]);
                    $std_tool_item_rel_id = DB::table('a_testdata_key')->where('id', $idMap)->value('std_tool_item_rel_id');
                    DB::table('a_std_tool_item_rel')->where('id', $id)->update(['id_map'=>$std_tool_item_rel_id]);
                    DB::commit();
                }catch(\Exception $e){
                    DB::rollBack();
                    return 0;
                }
                return 1;
            }
            //找不到的对应关系
            $unmapcount = 0;
            DB::beginTransaction();
            try {
                //更新映射关系id
                DB::table('a_std_tool_item_rel')->where('id',$id)->update(['id_map'=>$idMap]);
                //找到映射数据分别映射
                $data = DB::table('a_testdata_key')->where('std_tool_item_rel_id', $id)->pluck('data_unit','data_key');
                $mapdata = DB::table('a_testdata_key')->where('std_tool_item_rel_id', $idMap)->select('id','data_key','data_unit')->get();
                $kmapdata = [];
                foreach ($mapdata as $item){
                    $key = $this->dealstr($item->data_key);
                    $kmapdata[$key] = $item;
                }
                foreach ($data as $key => $unit){
                    $dkey = $this->dealstr($key);
                    if(isset($kmapdata[$dkey]) && $this->dealstr($kmapdata[$dkey]->data_unit) === $this->dealstr($unit)){
                        DB::table('a_testdata_key')->where('std_tool_item_rel_id', $id)->where('data_key',$key)->update(['id_map'=>$kmapdata[$dkey]->id]);
                    }else{
                        DB::table('a_testdata_key')->where('std_tool_item_rel_id', $id)->where('data_key',$key)->update(['id_map'=>0]);
                        $unmapcount += 1;
                    }
                }
                DB::commit();
            }catch (Exception $e){
                DB::rollBack();
                return out(0,'映射失败');
            }
            $msg = $unmapcount == 0 ? '全部映射成功' : '有'.$unmapcount.'条没有映射，请手动映射';
            return out(1,$msg);
        }
        else {
            $data = DB::table('a_testdata_key')->where('std_tool_item_rel_id', $id)->orderBy('sort')->get()->toArray();
            if ($request->ajax()) {
                return $data;
            }
            $sql = "SELECT
                     A.id,
                     A.std_id,
                     A.tool_id,
                     A.testitem_id,
                     A.id_map,
                     B.num,
                     C.`name` AS itemname,
                     D.`name` AS toolname
                 FROM
                     a_std_tool_item_rel A
                 LEFT JOIN a_standard B ON A.std_id = B.id
                 LEFT JOIN a_testitem C ON A.testitem_id = C.id
                 LEFT JOIN a_tool D ON A.tool_id = D.id
                 WHERE
                     A.id = :id";

            $data_rel = DB::select($sql, ['id' => $id]); //参数绑定可以避免 SQL 注入攻击
            $data_rel = $data_rel[0];
            $map = [];
            if($data_rel->id_map){
                $map['info'] = DB::table('a_std_tool_item_rel')->find($data_rel->id_map);
                $map['data'] = DB::table('a_testdata_key')->where('std_tool_item_rel_id', $data_rel->id_map)->orderBy('sort')->get()->toArray();
            }
            $data_rel_o = DB::table('a_std_tool_item_rel as a')
                ->leftJoin('a_standard as b', 'a.std_id', '=', 'b.id')
                ->leftJoin('a_testitem as c', 'a.testitem_id', '=', 'c.id')
                ->where([['tool_id', '=', $data_rel->tool_id], ['std_id', '!=', $data_rel->std_id]])
                ->select('a.std_id', 'a.id', 'b.num', 'c.name')->get();
            $data_rel_other = [];
            foreach ($data_rel_o as $item) {
                $data_rel_other[$item->std_id][] = $item;
            }
            return view('admin.testdataKey.relation', compact('data_rel', 'data', 'data_rel_other','map'));
        }
    }

    //

    //关联数据标题
    public function dataTitle(Request $request)
    {
        $id = $request->id; //a_std_tool_item_rel ID

        if ($request->isMethod('POST')) {
            $data = $request->only(['std_id', 'tool_id', 'item_id', 'data_key', 'data_unit','line_num']);
            $data['std_tool_item_rel_id'] = $id;

            try {
                //插入数据    insert 返回true 或 false
                $result = DB::table('a_testdata_key')->insertGetId($data);
            } catch (\Throwable $e) {
                return '2';
            }

            //将排序字段更新为id
            DB::table('a_testdata_key')->where('id', $result)->update(['sort' => $result]);

            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result ? '1' : '0';
        }
        else {
            $data = DB::table('a_testdata_key')->where('std_tool_item_rel_id', $id)->orderBy('sort')->get();

            $sql = "SELECT
                     A.id,
                     A.std_id,
                     A.tool_id,
                     A.testitem_id,
                     B.num,
                     C.`name` AS itemname,
                     D.`name` AS toolname
                 FROM
                     a_std_tool_item_rel A
                 LEFT JOIN a_standard B ON A.std_id = B.id
                 LEFT JOIN a_testitem C ON A.testitem_id = C.id
                 LEFT JOIN a_tool D ON A.tool_id = D.id
                 WHERE
                     A.id = :id";

            $data_rel = DB::select($sql, ['id' => $id]); //参数绑定可以避免 SQL 注入攻击
            $data_rel = $data_rel[0];
            $excel_pos = DB::table('a_excel_pos as a')
                ->leftJoin('a_std_tool_rel as b','a.std_tool_rel_id','=','b.id')
                ->where([
                    'b.std_id'  => $data_rel->std_id,
                    'b.tool_id'  => $data_rel->tool_id,
                ])
                ->select('a.id', 'a.name','a.is_default')
                ->get();
            /*++++++++++++++++++ 默认模板位置初始化到表 start +++++++++++++++++++*/
            $in = [];
            foreach ($excel_pos as $item) {
                if($item->is_default == 1 && DB::table('a_excel_pos_testdata_key')->where('excel_pos_id',$item->id)->doesntExist()){
                    foreach ($data as $dataItem) {
                        $in[] = [
                            'excel_pos_id' => $item->id,
                            'testdata_key_id' => $dataItem->id,
                            'pos_start' => $dataItem->pos_start,
                            'pos_end' => $dataItem->pos_end,
                        ];
                    }
                    break;
                }
            }
            $in ? DB::table('a_excel_pos_testdata_key')->insert($in) : '';
            /*++++++++++++++++++ 默认模板位置初始化到表 end +++++++++++++++++++*/
            return view('admin.testdataKey.dataTitle', compact('data_rel', 'data','excel_pos'));
        }
    }

    //数据标题 删除
    public function del(Request $request)
    {
        $id = (int)$request->id;
        try {
            // delete 返回受影响的行数
            $result = DB::table('a_testdata_key')->where('id', $id)->delete();
        } catch (\Throwable $e) {
            if(DB::table('a_input_data')->where('testdata_key_id',$id)->exists()){
                return '该标题值已经被使用！';
            }
            return $e->getMessage();
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    //数据标题 隐藏
    public function hide(Request $request)
    {
        $id = (int)$request->id;
        $hide = (int)$request->hide;
        $result = 0;
        if ($id && !is_null($hide)) {
            $result = DB::table('a_testdata_key')->where('id', $id)->update(['ishide'=>$hide]);
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result > 0 ? '1' : '0';
    }

    /**
     * 排序 试验数据标题
     */
    public function saveSortTestdataKey(Request $request)
    {
        $excel_pos_id = $request->excel_pos_id;
        if (!$excel_pos_id) {
            return '请先指定模板！';
        }
        $isDef = DB::table('a_excel_pos')->where('id',$excel_pos_id)->value('is_default');
        $updatas = request('data',[]);
        DB::beginTransaction();
        try{
            $in = [];
            foreach ($updatas as $id => $updata) {
                if ($isDef === 1) {
                    DB::table('a_testdata_key')->where('id', $id)->update($updata);
                }
                $o_ep_tb = 'a_excel_pos_testdata_key';
                $where = ['excel_pos_id'=>$excel_pos_id,'testdata_key_id'=>$id];
                $up = ['pos_start'=>$updata['pos_start'],'pos_end'=>$updata['pos_end']];
                if (DB::table($o_ep_tb)->where($where)->exists()) {
                    DB::table($o_ep_tb)->where($where)->update($up);
                }else{
                    $in[] = array_merge($up,$where);
                }
            }
            DB::table($o_ep_tb)->insert($in);
            DB::commit();
        }catch(\Exception $exception){
            DB::rollBack();
            return "操作失败：".$exception->getMessage();
        }
        return 1;
    }
}
